package com.pwc.fusion.excelToDB;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

@Component
public class ExcelUtil implements IDataSet {

	private Workbook workBook;

	private final static String XSSFWorkbook = ".xlsx";

	private final static String HSSFWorkbook = ".xls";
	
	private Map<String,IDataAdaptor> dataAdaptors=new HashMap<String,IDataAdaptor>();
	
	
	public void initial(File file) throws IOException {
		
		InputStream in = new FileInputStream(file);
		
		initial(in,file.getName());
	}
	
	
	public void initial(InputStream in,String fileName) throws IOException
	{
		if (fileName.endsWith(XSSFWorkbook)) {
			this.workBook = new XSSFWorkbook(in);
		} else if (fileName.endsWith(HSSFWorkbook)) {
			this.workBook = new HSSFWorkbook(in);
		}
		
		 int sheetNumber=workBook.getNumberOfSheets();
		 
		 for(int i=0;i<sheetNumber;i++ ){
			 Sheet sheet=workBook.getSheetAt(i);
			 formatSheet( sheet);
		 }
	}
	
	private void formatSheet(Sheet sheet)
	{
		IDataAdaptor adaptor=new ExcelSheet();
		adaptor.setName(sheet.getSheetName());
		
		List<String[]> result=new ArrayList<String[]>();
		Iterator<Row> rowIterator = sheet.iterator();		
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			result.add(getRow(row));
		}
		adaptor.setAllData(result);
		
		dataAdaptors.put(sheet.getSheetName(), adaptor);
	}
	
	private String[] getRow(Row row) {
		List<String> rowList = new ArrayList<String>();

		Iterator<Cell> cellIterator = row.cellIterator();

		while (cellIterator.hasNext()) {
			Cell cell = cellIterator.next();

			cell.setCellType(Cell.CELL_TYPE_STRING);
			
			String value = cell.getStringCellValue();
			if(!value.trim().equals("")){
			rowList.add(value);
			}
		}

		String[] result = rowList.toArray(new String[rowList.size()]);

		return result;
	}
	
	

	public Map<String, IDataAdaptor> getDataSet() {
		
		return dataAdaptors;
	}

	public void addDataAdaptor(String sheetName,IDataAdaptor dataAdaptor) {
		
		dataAdaptors.put(sheetName,dataAdaptor);
	}

	@Override
	public IDataAdaptor getDataAdaptor(String sheetName) {
		return dataAdaptors.get(sheetName);
	}
	
	

}
